Specify criteria using filter by form or advanced filter
NOTE: Filtering is the process of searching the database for records that meet specific criteria.
Do one of the following:
1) Specify filter criteria using Filter By Form:
a) Choose the 'Look For' tab at the bottom of the Filter by Form window.
b) Click in the field in which to specify criteria for the records. (A down arrow appears.)
c) Click on the arrow. (A drop-down list appears.)
d) Select the desired value for the criteria from the drop-down list.
NOTE: The values are the various entries in the field.
e) Repeat steps 1)b) through 1)d), clicking in the appropriate column, for each field for which to specify filter criteria.
NOTE: If values are specified in more than one field, the filter returns records ONLY if they meet the criteria in all the fields specified. In other words, the filter acts as an AND statement.
f) (Optional) To specify alternative values to include in the filter:
1] Choose the 'Or' tab at the bottom of the window. (A new sheet appears.)
2] Click in the field in which to specify criteria on the 'Or' tab. (A down arrow appears.)
3] Click on the arrow. (A drop-down list appears.)
4] Select the desired value for the criteria from the drop-down list.
5] Repeat steps 1)f)2] through 1)f)4], clicking in the appropriate column, for each field for which to specify criteria on the 'Or' tab.
6] Repeat steps 1)f)1] through 1)f)5] for each set of alternative values to specify.
NOTE: The filter returns records that have ALL the values specified on the 'Look For' tab OR all the values specified on the second tab OR the third tab OR the fourth, etc. In other words, the filter acts as an OR statement.
2) Do one of the following to specify filter criteria using Advanced Filter/Sort:
NOTE: Do the following in the first column in the design grid at the bottom of the window.
a) To sort on certain field(s):
1] Click in the 'Field' row. (A down arrow appears.)
2] Click on the arrow. (A drop-down list appears.)
3] Select the first field on which to sort from the 'Field' drop-down list.
4] Click in the 'Sort' row. (A down arrow appears.)
5] Click on the arrow. (A drop-down list appears.)
6] Select 'Ascending', 'Descending', or '(not sorted)' from the 'Sort' drop-down list.
7] Repeat steps 2)a)1] through 2)a)6] for each field to sort on.
b) To enter simple criteria for the filter:
1] Enter the desired expression in the 'Criteria' row for the desired field.
2] Enter the desired expression in the 'or' rows' for the same field.
3] Repeat steps 2)b)1] and 2)b)3] for each field for which to enter known criteria.
c) To enter an unknown expression as criteria:
1] Right-click in the 'Criteria' row. (A menu papers.)
2] Select 'Build...' from the menu. (The Expression Builder dialog box appears.)
3] Select the object from which to use fields from the first list box. (A list of fields appears in the second list box.)
NOTE: If a '+' appears on the folder for an object, double-click on the plus to view the objects below the specific type.
4] Select a field from the second list box.
5] Choose the 'Paste' button. (The selected field appears with its table name in the expression box at the top of the dialog box.)
6] Choose the operator button for the desired calculation. (The symbol appears next in the expression box at the top of the dialog box.)
7] Do one or both of the following:
a] Select the second field to include in the expression.
b] Enter a desired integer number to include in the expression.
8] Choose the 'Paste' button. (The selected field appears next in the expression box at the top of the dialog box.)
9] Repeat steps 2)c)3] or 2)c)4] through 2)c)8] until the expression is completed.
NOTE 1: Fields from various tables can be used together for the expression.
NOTE 2: Choose the 'Undo' button to undo the last action (i.e., the last field or symbol added to the expression box).
10] When the expression is complete, choose the 'OK' button. (The expression appears in the 'Criteria' row of the advanced filter/sort window.)
NOTE: Choose the 'Cancel' button to cancel the entire expression.
11] Repeat steps 2)c)1] through 2)c)10] in the 'or' row, enter additional criteria for that field.
NOTE: 'or' means that one or the other of the inputted criteria must be true. When the 'or' row is used, either the criteria entered in the 'Criteria' row OR the criteria entered in the 'or' row must be true in order for a record to be included.
12] Repeat step 2)c)11] for each additional criterion to add in the 'or' rows for THAT field.
13] Repeat steps 2)c)1] through 2)c)12] for each field for which to enter an unknown expression.